from time import sleep

import pymysql,datetime,requests,urllib,json
import baostock as bs
import pandas as pd





class DataManagement:
    """
    数据操作类
    """
    #连接数据库
    newstocklist = []
    def connect_sql(self):
        conn = pymysql.connect("localhost", "root", "123456", db="kaipanla", charset='utf8', port=3306)
        self.cursor = conn.cursor()
        return self.cursor
    #关闭游标
    def cursor_close(self):
        self.cursor_close = self.cursor.close()
        return self.cursor_close
    #获取stocklist列表的总行数
    def stocklist_zong_hang_shu(self):
        zong_hang_shu = self.cursor.execute("select * from stocklist ;")

        return zong_hang_shu
    #获取指定交易日数据并排序
    def zhi_ding_jiao_yi_ri_shu_ju(self,duo_ri):
        sql1 = "select *  from kaipanla where date = '%s' order by num desc;" % duo_ri
        self.cc = self.cursor.execute(sql1)
        return self.cc
    def qu_chong(self):

        for i in range(self.cc):
            rowCount = self.cursor.fetchone()
            self.newstocklist.append(rowCount[1])
            return self.newstocklist

    def hang_qing_shu_ju(self,data):
        #### 登陆系统 ####
        lg = bs.login()

        if data[0:3] =="600":
            rs = bs.query_history_k_data_plus("sh.%s",
                                          "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST",
                                          start_date='2017-07-01', end_date='2017-12-31',
                                          frequency="d", adjustflag="3") % data

        else:
            rs = bs.query_history_k_data_plus("sz.%s",
                                              "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST",
                                              start_date='2017-07-01', end_date='2017-12-31',
                                              frequency="d", adjustflag="3") % data

        data_list = []
        while (rs.error_code == '0') & rs.next():
            # 获取一条记录，将记录合并在一起
            data_list.append(rs.get_row_data())
        result = pd.DataFrame(data_list, columns=rs.fields)
        #### 结果集输出到csv文件 ####

        #### 登出系统 ####

    def ban_kuai_xuan_gu(self,count,data):


        base_url = "https://hq.kaipanla.com/w1/api/index.php?Order=1&st=%s&a=ZhiShuStockList_W8&c=ZhiShuRanking" \
                   "&PhoneOSNew=1&old=1&DeviceID=ffffffff-f840-6366-ffff-fffffcce49ce&Token=8d565cec16f434e64364c4480d9dfa3b" \
                   "&Index=0&apiv=w19&Type=6&UserID=584121&PlateID=%s&" % (count,data)

        response = urllib.request.urlopen(base_url)
        # 获取原始数据
        data_yuan = response.read()
        data_1 = json.loads(data_yuan)
        return data_1
    def pan_duan_jiao_yi_ri(self):
        d_time = datetime.datetime.strptime(str(datetime.datetime.now().date()) + '9:30', '%Y-%m-%d%H:%M')
        d_time1 = datetime.datetime.strptime(str(datetime.datetime.now().date()) + '15:00', '%Y-%m-%d%H:%M')
        n_time = datetime.datetime.now()
        a = 0
        if  n_time >= d_time and n_time <= d_time1:
            a = 1
        else:
            a = 0
            return a

    def huo_qu_dang_ri_hang_qing(self, count, data):

        StockID = "StockID=%s" % (count)
        Day = "Day=%s"%(data)
        base_url = "https://pchq.kaipanla.com/w1/api/index.php?c=PCArrangeData&a=GetHQPlate&%s&%s&"% ( StockID, Day)
        base_url2 = "SelType=1%2C2%2C3%2C8%2C9%2C5%2C6%2C7&UserID=583502&Token=56e01ded1ff61c21304a5c8026b90fc6"
        base_url3= base_url + base_url2
        print(base_url3)
        response = urllib.request.urlopen(base_url3)
        # 获取原始数据
        data_yuan = response.read()
        data_1 = json.loads(data_yuan)
        data_2= data_1['pankou']['real']['px_change_rate']
        a = self.pan_duan_jiao_yi_ri()
        while a == 1 :


            sleep(30)















